# -*- coding:utf-8 -*-

__author__ = 'shouke'

import os
import traceback
import configparser
from pypinyin import lazy_pinyin
from datetime import datetime
from database.db_cli import MyDBClient
from utils.log import logger

class ZenTaoCli:
    def __init__(self, auto_create_product = 1, auto_create_module = 1, is_version_pro = 0):
        try:
            config = configparser.ConfigParser()

            config.read(os.path.join(os.path.split(os.path.dirname(__file__))[0], 'conf/database.conf'), encoding='utf-8')
            database_config = config['ZENTAO']

            self.zentao_dbclient = None
            self.zentao_dbclient = MyDBClient(database_config.get('db_name'),
                                         database_config.get('host'),
                                         database_config.get('port'),
                                         database_config.get('user'),
                                         database_config.get('password'),
                                         database_config.get('charset'))

            self.product_id_cache = {}
            self.module_id_cache = {}
            self.auto_create_product = auto_create_product
            self.auto_create_module = auto_create_module
            self.is_version_pro = is_version_pro

        except Exception as e:
            logger.error('初始化数据库客户端出错:%s' % traceback.format_exc())
            exit()

    def query_product_by_name(self, product_name):
        query_sql = f"SELECT id " \
                    "FROM zt_product " \
                    f"WHERE name = '{product_name}' AND deleted = '0' ORDER BY createdDate DESC LIMIT 1;"
        res = self.zentao_dbclient.select(query_sql, dictionary=True)
        if res.get('id'):
            return res.get('id')
        return

    def create_product_by_name(self, product_name):
        order = 5
        res = self.zentao_dbclient.select("SELECT MAX(`order`) AS `order` FROM zt_product WHERE deleted = '0';", dictionary=True)
        if res.get('order'):
            order = int(res.get('order')) + 5

        product_code = ''.join(lazy_pinyin(product_name)).upper()
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        insert_sql = f"INSERT INTO `zt_product`(`name`, `code`, `line`, `type`, `status`, `desc`, `PO`, `QD`, `RD`, `acl`, `whitelist`," \
                     "`createdBy`, `createdDate`, `createdVersion`, `order`, `deleted`) " \
                     f"VALUES ('{product_name}', '{product_code}', 0, 'normal', 'normal', '', 'admin', 'admin', 'admin', " \
                     f" 'open', 'product auto create by zen tao testcase helper', 'admin', '{current_time}', '10.3.1', {order}, '0');"
        if self.is_version_pro:
            insert_sql = f"INSERT INTO `zt_product`(`program`, `name`, `code`, `line`, `type`, `status`, `desc`, `PO`, `QD`, `RD`, `acl`, `whitelist`," \
                         "`createdBy`, `createdDate`, `createdVersion`, `order`, `deleted`) " \
                         f"VALUES (0, '{product_name}', '{product_code}', 0, 'normal', 'normal', '', 'admin', 'admin', 'admin', " \
                         f" 'open', 'product auto create by zen tao testcase helper', 'admin', '{current_time}', '10.3.1', {order}, '0');"

        product_id = self.zentao_dbclient.insert(insert_sql)
        if product_id is None:
            raise  Exception(f'创建数据库产品名称失败，期望创建产品名称：{product_name}' )
        return product_id


    def create_module_by_module_path(self, module_path):
        '''根据模块路径批量创建模块'''


        module_path = module_path.strip().strip('/')
        if not module_path:
            logger.error('产品模块路径为空，创建产品模块失败')
            raise Exception('产品模块路径为空，创建产品模块失败')

        module_path_copy = module_path
        module_path = module_path.split('/')
        product_name = module_path[0].strip()
        if not product_name:
            raise Exception('获取的模块所属产品为空，创建模块失败')

        if module_path_copy in self.module_id_cache:
            return (self.module_id_cache.get(module_path_copy), self.product_id_cache.get(product_name))

        if product_name in self.product_id_cache:
            product_id = self.product_id_cache[product_name]
        else:
            product_id = self.query_product_by_name(product_name)
            if product_id is None:
                if self.auto_create_product:
                    product_id = self.create_product_by_name(product_name)
                else:
                    raise Exception(f'模块所属产品{product_name}不存在，创建模块失败')
            self.product_id_cache[product_name] = product_id

        parent_id = 0
        path = ','
        for index, module_name in enumerate(module_path[1:]):
            current_module_path = '/'.join(module_path[:index+2])

            if current_module_path in self.module_id_cache:
                module_id = self.module_id_cache[current_module_path]
                path += str(module_id) + ','
                parent_id = module_id
                continue

            module_id = self.query_module_by_module_name(module_name, product_id, parent_id)
            if module_id is None:
                if self.auto_create_module:
                    module_id = self.create_module_by_module_name(module_name,  product_id,  parent_id, index+1, path)
                else:
                    raise Exception(f'用例所属模块{current_module_path}所不存在' )

            path += str(module_id) + ','
            self.module_id_cache[current_module_path] = module_id
            parent_id = module_id

        return (module_id, product_id)



    def query_module_by_module_name(self, module_name, product_id, parent_id):
        '''根据模块名称查询产品模块'''

        query_sql = f"SELECT id " \
                    "FROM zt_module " \
                    f"WHERE name = '{module_name}' AND root = {product_id} AND parent = {parent_id} AND deleted = '0' ORDER BY id DESC LIMIT 1;"

        res = self.zentao_dbclient.select(query_sql, dictionary=True)
        if res.get('id'):
            return res.get('id')
        return None

    def create_module_by_module_name(self, module_name, product_id, parent_id, level, path):
        '''根据模块名称创建产品模块'''

        query_sql = f"SELECT MAX(`order`) AS `order` " \
                    "FROM zt_module " \
                    f"WHERE root = {product_id} AND parent = {parent_id} AND deleted = '0';"

        order = 10
        res = self.zentao_dbclient.select(query_sql, dictionary=True)
        if res.get('order'):
            order = int(res.get('order')) + 10

        insert_sql = "INSERT INTO `zt_module`(`root`, `branch`, `name`, `parent`, `path`, `grade`, `order`, `type`, `owner`, `collector`, `short`, `deleted`) " \
                     f"VALUES ({product_id}, 0, '{module_name}', {parent_id}, '', {level}, {order}, 'case', '', '', '', '0');"
        module_id = self.zentao_dbclient.insert(insert_sql)
        if module_id is None:
            raise Exception(f'创建模块失败，模块名称：{module_name}' )
        module_id_path = path + f'{module_id},'
        update_sql = f"UPDATE `zt_module` SET path = '{module_id_path}' WHERE id = {module_id};"
        self.zentao_dbclient.update(update_sql)
        return module_id


    def create_testcase(self, product_id, module_id, testcase_name, preconditions, priority, testcase_type, stages, casesteps):
        '''创建测试用例
        :param stage: unittest -- 单元测试阶段, feature -- 功能测试阶段, intergrate -- 集成测试阶段, system -- 系统测试阶段, smoke -- 冒烟测试阶段, bvt -- 版本验证阶段
        :param testcase_type: feature -- 功能测试, interface -- 接口测试 performance--性能测试，install--安装部署 config--配置测试, security--安全相关 other--其它
        '''

        query_sql = f"SELECT id, version " \
                    "FROM zt_case " \
                    f"WHERE title = '{testcase_name}' AND product = {product_id} AND module = {module_id} AND deleted = '0' ORDER BY id DESC LIMIT 1;"

        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        res = self.zentao_dbclient.select(query_sql, dictionary=True)
        case_id = res.get('id')
        if case_id is not None:
            self.update_testcase(case_id, preconditions, priority, testcase_type, stages, res.get('version'), casesteps, product_id)
            return

        insert_sql = "INSERT INTO `zt_case`(`product`, `branch`, `lib`, `module`, `path`, `story`, `storyVersion`, `title`, `precondition`, " \
                     "`keywords`, `pri`, `type`, `stage`, `howRun`, `scriptedBy`, `scriptedDate`, `scriptStatus`, " \
                     " `scriptLocation`, `status`, `color`, `frequency`, `order`, `openedBy`, `openedDate`, `reviewedBy`, `reviewedDate`," \
                     " `lastEditedBy`, `lastEditedDate`, `version`, `linkCase`, `fromBug`, `fromCaseID`, `deleted`, `lastRunner`, `lastRunDate`, `lastRunResult`) "\
                     f"VALUES ({product_id}, 0, 0, {module_id}, 0, 0, 1, '{testcase_name}', '{preconditions}', '', {priority}, '{testcase_type}', '{stages}', '', '', " \
                     f"'0000-00-00', '', '', 'normal', '', '1', 0, 'admin', '{current_time}', '', '0000-00-00', '', '0000-00-00 00:00:00', 1, '', 0, 0, '0', '', '0000-00-00 00:00:00', '')";
        if self.is_version_pro == 1:
            insert_sql = "INSERT INTO `zt_case`(`project`, `product`, `branch`, `lib`, `module`, `path`, `story`, `storyVersion`, `title`, `precondition`, " \
                         "`keywords`, `pri`, `type`, `frame`, `stage`, `howRun`, `scriptedBy`, `scriptedDate`, `scriptStatus`, " \
                         " `scriptLocation`, `status`, `subStatus`,  `color`, `frequency`, `order`, `openedBy`, `openedDate`, `reviewedBy`, `reviewedDate`," \
                         " `lastEditedBy`, `lastEditedDate`, `version`, `linkCase`, `fromBug`, `fromCaseID`, `deleted`, `lastRunner`, `lastRunDate`, `lastRunResult`, `execution`) " \
                         f"VALUES (0, {product_id}, 0, 0, {module_id}, 0, 0, 1, '{testcase_name}', '{preconditions}', '', {priority}, '{testcase_type}', '', '{stages}', '', '', " \
                         f"'0000-00-00', '', '', 'normal', '', '', '1', 0, 'admin', '{current_time}', '', '0000-00-00', '', '0000-00-00 00:00:00', 1, '', 0, 0, '0', '', '0000-00-00 00:00:00', '', 0)";

        testcase_id = self.zentao_dbclient.insert(insert_sql)
        if testcase_id is None:
            raise Exception(f'创建测试用例失败，用例名称：f{testcase_name}')
        self.create_testcase_steps(testcase_id, casesteps)


    def format_expcetions(self, expections):
        '''格式化期望'''

        result = ''

        if len(expections) == 1:
            result = expections[0]
        else:
            for idx, expection in enumerate(expections):
                result += f'{idx + 1}) ' + expection + '\n'
        return result.rstrip('\n')


    def create_testcase_steps(self, testcase_id, casesteps):
        '''创建测试用例步骤'''

        values = []
        for casestep_data in casesteps:
            values.append((0, testcase_id, 1, 'step', casestep_data.get('action'), self.format_expcetions(casestep_data.get('expections'))))

        if values:
            insert_sql = "INSERT INTO `zt_casestep`(`parent`, `case`, `version`, `type`, `desc`, `expect`) VALUES ('%s', '%s', '%s', %s, %s, %s);"
            self.zentao_dbclient.insert_many(insert_sql, values)

    def update_testcase(self, testcase_id, preconditions, priority, testcase_type, stages, old_version, casesteps, product_id):
        # 更新用例信息

        update_sql = f"UPDATE `zt_case` SET `precondition` = '{preconditions}', `pri` = {priority}, `type` = '{testcase_type}', "\
                     f"`stage` = '{stages}', `lastEditedDate` = NOW() " \
                     f" WHERE id = {testcase_id}"
        self.zentao_dbclient.update(update_sql)
        self.update_testcase_steps(testcase_id, casesteps, old_version, product_id)


    def update_testcase_steps(self, testcase_id, casesteps, old_version, product_id):
        '''更新测试用例步骤
        :@:param： exec_updating_check 执行更新检查，True则需要检查，根据检查结果判断是否更新，否则不检查，直接更新
        '''

        need_to_update_steps = False
        values = []
        query_sql = f"SELECT `desc` AS 'action', `expect` AS 'expections'" \
                    "FROM zt_casestep " \
                    f"WHERE `case` = {testcase_id} AND `version` = {old_version} ORDER BY id ASC;"
        steps = self.zentao_dbclient.select_many(query_sql, dictionary=True)

        new_version = old_version + 1

        # old_steps = ''
        # new_steps = ''
        if len(steps) != len(casesteps):
            need_to_update_steps = True
            # for index, step_data in enumerate(steps):
            #     action = step_data.get('desc')
            #     expections = step_data.get('expect')
            #     old_steps += action + ' EXPECT:' + expections + ';\n'

            for casestep_data in casesteps:
                action = casestep_data.get('action')
                expections = self.format_expcetions(casestep_data.get('expections'))
                # new_steps += action + ' EXPECT:' + expection + ';\n'
                values.append((0, testcase_id, new_version, 'step', action, expections))
        else:
            # new_steps = ''
            for index, step_data in enumerate(steps):
                old_action = step_data.get('action')
                old_expections = step_data.get('expections')

                new_action = casesteps[index].get('action')
                new_expections = self.format_expcetions(casesteps[index].get('expections'))
                if old_action != new_action:
                    need_to_update_steps = True
                # old_steps += old_action
                # new_steps += casesteps[index].get('action')

                if old_expections != new_expections:
                    need_to_update_steps = True
                # old_steps += old_action + ' EXPECT:' + old_expection + ';\n'
                # new_steps += new_action + ' EXPECT:' + new_expection + ';\n'
                values.append((0, testcase_id, new_version, 'step', new_action, new_expections))

        if need_to_update_steps:
            update_sql = f"UPDATE `zt_case` SET `version` = {new_version}, lastEditedBy='admin', lastEditedDate=NOW()  WHERE `id` = {testcase_id} AND `version` = {old_version}"
            self.zentao_dbclient.update(update_sql)

            insert_sql = "INSERT INTO `zt_casestep`(`parent`, `case`, `version`, `type`, `desc`, `expect`) VALUES ('%s', '%s', '%s', %s, %s, %s);"
            self.zentao_dbclient.insert_many(insert_sql, values)


            # 操作记录
            insert_sql = f"INSERT INTO `zt_action`(`objectType`, `objectID`, `product`, `project`, `actor`, `action`, `date`, `comment`, `extra`, `read`) " \
                         f"VALUES ('case', {testcase_id}, ',{product_id},', 0, 'admin', 'edited', NOW(), '', '', '0')"
            if self.is_version_pro:
                insert_sql = f"INSERT INTO `zt_action`(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`, `date`, `comment`, `extra`, `read`) " \
                             f"VALUES ('case', {testcase_id}, ',{product_id},', 0, 0, 'admin', 'edited', NOW(), '', '', '0')"
            self.zentao_dbclient.insert(insert_sql)

            # insert_sql = f"INSERT INTO `zt_history`(`action`, `field`, `old`, `new`, `diff`) VALUES (12, 'version', '{case_version}', '{new_case_version}', '')"
            # self.zentao_dbclient.insert(insert_sql)
            # insert_sql = f"INSERT INTO `zt_history`(`action`, `field`, `old`, `new`, `diff`) VALUES (12, 'steps', '{old_steps}', '{new_steps}', '');"
            # self.zentao_dbclient.insert(insert_sql)


    def remove_testcase(self, product_id, module_id, testcase_name):
        '''逻辑删除测试用例'''

        query_sql = f"SELECT id " \
                    "FROM zt_case " \
                    f"WHERE title = '{testcase_name}' AND product = {product_id} AND module = {module_id} AND deleted = '0' ORDER BY id DESC LIMIT 1;"

        res = self.zentao_dbclient.select(query_sql, dictionary=True)
        testcase_id = res.get('id')
        if testcase_id is not None:
            update_sql = f"UPDATE `zt_case` SET `deleted` = '1', `lastEditedDate` = NOW() " \
                         f"WHERE id = {testcase_id}"
            self.zentao_dbclient.update(update_sql)


    def close(self):
        if self.zentao_dbclient:
            self.zentao_dbclient.close()


# if __name__ == '__main__':
    # zentao_cli = ZenTaoCli()
    # zentao_cli.create_product_by_name('产品3')
    # zentao_cli.create_module_by_module_path('/产品3/root/子产品模块1/子产品模块1-1/子产品模块1-1-1/')
    # zentao_cli.create_module_by_module_path('/产品3/root/子产品模块2/子产品模块2-1/子产品模块2-1-1/')

    # zentao_cli.close()

